package co.th.bjc.md.helper;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import co.th.bjc.md.common.ConnectDB;
import co.th.bjc.md.model.HospitalDepartment;

public class HospitalDepartmentHelper {
	
	private static Logger _log = Logger.getLogger(HospitalDepartmentHelper.class);
	
	public List<HospitalDepartment> findHospitalDepartmentByHospital(BigDecimal hospital) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<HospitalDepartment> listHospitalDepartment = new ArrayList<HospitalDepartment>(0);
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM hospital_department WHERE hospital_code = ?";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, hospital);
			
			rs = stmt.executeQuery();
			while (rs.next()){
				listHospitalDepartment.add(this.setHospitalDepartment(rs));
			}
		}catch(Exception ex){
			_log.error("findHospitalDepartmentByHospital --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return listHospitalDepartment;
	}
	
	public HospitalDepartment findHospitalDepartmentByHospitalAndDepartment(BigDecimal hospital, BigDecimal department) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		HospitalDepartment hosDept = null;
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM hospital_department WHERE hospital_code = ? AND hospital_dept_code = ?";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, hospital);
			stmt.setBigDecimal(2, department);
			
			rs = stmt.executeQuery();
			while (rs.next()){
				hosDept = this.setHospitalDepartment(rs);
			}
		}catch(Exception ex){
			_log.error("findHospitalDepartmentByHospitalAndDepartment --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return hosDept;
	}
	
	private HospitalDepartment setHospitalDepartment(ResultSet rs) throws Exception{
		HospitalDepartment hosDept = new HospitalDepartment();
		hosDept.setHospitalCode(rs.getBigDecimal("hospital_code"));
		hosDept.setHospitalDepartmentCode(rs.getBigDecimal("hospital_dept_code"));
		hosDept.setHospitalDepartmentNameTh(rs.getString("hospital_dept_name_th"));
		hosDept.setHospitalDepartmentNameEn(rs.getString("hospital_dept_name_en"));
		hosDept.setDirector(new PersonHelper().findPersonByPersonCode(rs.getBigDecimal("director_code")));
		hosDept.setBedCapacityMin(rs.getBigDecimal("bed_capacity_min"));
		hosDept.setBedCapacityMax(rs.getBigDecimal("bed_capacity_max"));
		return hosDept;
	}
	
	public HospitalDepartment save(Connection conn, HospitalDepartment hosDept) throws Exception{
		boolean isConnNull = false;
		ConnectDB connectDB = null;
		try {
			if (conn == null){
				connectDB = new ConnectDB();
				conn = connectDB.getConnection();
				conn.setAutoCommit(true);
				isConnNull = true;
			}

			hosDept = insert(conn, hosDept);
		} catch (Exception ex) {
			ex.printStackTrace();
			_log.error("Save HospitalDepartment failed. Detail : " + ex.getMessage());
		}finally{
			if (conn != null && isConnNull){
				connectDB.close();
			}
		}
		return hosDept;
	}
	
	final String SQL_INSERT = "INSERT INTO hospital_department (hospital_code, hospital_dept_code, hospital_dept_name_th, hospital_dept_name_en, director_code, bed_capacity_min, bed_capacity_max) VALUES (?, ?, ?, ?, ?, ?, ?);";
	private synchronized HospitalDepartment insert(Connection conn, HospitalDepartment hosDept) throws Exception{
		PreparedStatement stmt = null;
		try {
			/* Generate HospitalCode */
			BigDecimal hospitalCode = generateHospitalDepartmentCode(conn, hosDept.getHospitalCode());
			_log.info("Hospital Code : " + hospitalCode);
			hosDept.setHospitalCode(hospitalCode);
			
			/* Insert Person(Director) */
			if (hosDept.getDirector() != null){
				hosDept.setDirector(new PersonHelper().save(conn, hosDept.getDirector()));
			}
			
			stmt = conn.prepareStatement(SQL_INSERT);
			stmt.setBigDecimal(1, hosDept.getHospitalDepartmentCode());
			stmt.setString(2, hosDept.getHospitalDepartmentNameTh());
			stmt.setString(3, hosDept.getHospitalDepartmentNameEn());
			stmt.setBigDecimal(4, hosDept.getDirector().getPersonCode());
			stmt.setBigDecimal(5, hosDept.getBedCapacityMin());
			stmt.setBigDecimal(6, hosDept.getBedCapacityMax());
//			stmt.setString(11, CommonUtils.chkStringIsNull(hosDept.getCreateBy())?"SYSTEM":hosDept.getCreateBy());
//			stmt.setDate(12, new java.sql.Date(new Date().getTime()));
//			stmt.setString(13, "SYSTEM");
//			stmt.setDate(14, new java.sql.Date(new Date().getTime()));
			
			stmt.executeUpdate();
			_log.warn("Insert Hospital Department By HospitalCode : " + hosDept.getHospitalCode());
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (stmt != null) stmt.close();
		}
		return hosDept;
	}
	
	private BigDecimal generateHospitalDepartmentCode(Connection conn, BigDecimal hospitalCode) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT MAX(hospital_dept_code) + 1 AS DEPT_CODE FROM hospital_department WHERE hospital_code = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, hospitalCode);
			
			rs = stmt.executeQuery();
			if(rs.next()){
				return rs.getBigDecimal("DEPT_CODE");
			}
			throw new Exception("Can't generate HospitalDepartmentCode");
		}catch(Exception ex){
			_log.error("generateHospitalDepartmentCode --> " + ex.toString());
			throw new Exception("Can't generate HospitalDepartmentCode : " + ex.getMessage());
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
		}
	}
}
